from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,ForeignKey,String
from sqlalchemy.orm import relationship


from SQLAlchemy import constants

IP=constants.IP
# db_connect_string='mysql://liu:liu@121.41.203.238:3306/bishe?charset=utf8'
db_connect_string='mysql://liu:liu@'+IP+':3306/bishe?charset=utf8'
engine = create_engine(db_connect_string)
Base = declarative_base(engine)

class Admin(Base):
    __tablename__='admin'
    admin_id=Column(Integer,primary_key=True)
    name=Column(String(10))
    gender=Column(String(10))
    telephone = Column(String(50))


#登录人员信息表
class LogIn(Base):
    __tablename__ = 'login'
    login_id = Column(Integer, primary_key=True)
    password = Column(String(20),default='123456')

#老师表
class Teacher(Base):
    __tablename__='teacher'
    teacher_id=Column(Integer,primary_key=True)
    name=Column(String(50))
    gender=Column(String(10))
    telephone=Column(String(50))
    course_id=Column(Integer,ForeignKey('course.course_id'))

    choise_courses = relationship('ChoiseCourse', backref='teacher', cascade='all')
    assesss = relationship('Assess', backref='teacher', cascade='all')
    designs = relationship('Design', backref='teacher', cascade='all')
    school_timetables = relationship('SchoolTimeTable', backref='teacher', cascade='all')
    class_teachers = relationship('ClassTeacher', backref='teacher', cascade='all')
#选课表
class ChoiseCourse(Base):
    __tablename__='choisecourse'
    teacher_id=Column(Integer,ForeignKey('teacher.teacher_id'),primary_key=True)
    student_id=Column(Integer,ForeignKey('student.student_id'),primary_key=True)
#学生表
class Student (Base):
    __tablename__='student'
    student_id=Column(Integer,primary_key=True)
    name=Column(String(20))
    gender=Column(String(5))
    telephone=Column(String(20))
    class_id=Column(Integer,ForeignKey('class.class_id'))

    choise_courses = relationship('ChoiseCourse', backref='student', cascade='all')
    marks = relationship('Mark', backref='student', cascade='all')
    assesss = relationship('Assess', backref='student', cascade='all')
    # designs = relationship('Design', backref='student', cascade='all')
#专业课表
class Course (Base):
    __tablename__='course'
    course_id=Column(Integer,primary_key=True)
    course=Column(String(20))

    teachers=relationship('Teacher',backref='course',cascade='all')
    marks=relationship('Mark',backref='course',cascade='all')
#成绩表
class Mark(Base):
    __tablename__='mark'
    mark_id=Column(Integer,primary_key=True)
    student_id=Column(Integer,ForeignKey('student.student_id'),primary_key=True)
    course_id=Column(Integer,ForeignKey('course.course_id'))
    mark=Column(String(5))
#时间表
class TimeTable (Base):
    __tablename__='timetable'
    time_id=Column(Integer,primary_key=True)
    week=Column(String(20))
    time=Column(String(20))

    school_timetables=relationship('SchoolTimeTable',backref='timetable',cascade='all')
#课程表
class SchoolTimeTable(Base):
    __tablename__='schooltimetable'
    school_time_table_id=Column(Integer,  primary_key=True)
    time_id=Column(Integer,ForeignKey('timetable.time_id'))
    classroom_id = Column(Integer, ForeignKey('classroom.classroom_id'))
    class_id = Column(Integer, ForeignKey('class.class_id'))
    teacher_id = Column(Integer, ForeignKey('teacher.teacher_id'))
#班级老师连接表
class ClassTeacher(Base):
    __tablename__='classteacher'
    teacher_id = Column(Integer, ForeignKey('teacher.teacher_id'), primary_key=True)
    class_id = Column(Integer, ForeignKey('class.class_id'), primary_key=True)
#教师评价表
class Assess(Base):
    __tablename__='assess'
    assess_id=Column(Integer,  primary_key=True)
    teacher_id = Column(Integer, ForeignKey('teacher.teacher_id'), primary_key=True)
    student_id = Column(Integer, ForeignKey('student.student_id'), primary_key=True)
    content=Column(String(500))
#毕设表
class Design(Base):
    __tablename__='design'
    design_id=Column(Integer,primary_key=True)
    teacher_id = Column(Integer, ForeignKey('teacher.teacher_id'))
    student_id = Column(Integer, ForeignKey('student.student_id'))
    name=Column(String(50))
#班级表
class Class(Base):
    __tablename__='class'
    class_id=Column(Integer,primary_key=True)
    major=Column(String(50))
    grade=Column(Integer)
    clas=Column(String(50))

    school_timetables = relationship('SchoolTimeTable', backref='class', cascade='all')
    class_teachers=relationship('ClassTeacher',backref='class',cascade='all')
    students=relationship('Student',backref='class',cascade='all')


class Classroom(Base):
    __tablename__='classroom'
    classroom_id=Column(Integer,primary_key=True)
    name=Column(String(10))

    school_time_tables=relationship('SchoolTimeTable',backref='classroom',cascade='all')





Base.metadata.create_all()
